﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
	public class ReceiverBD
	{
		public List<FieldsTable> getData()
		{
			//string ConnectionStringKey = "SUPSDB";
			string connectionString = "Server=.\\SQLExpress;AttachDbFilename=E:\\TFS_DD\\railwaytracking2013\\Personal\\Ilya Afanasev\\DigDes.DSchool.SUPS\\Database\\SUPS.mdf;Database=SUPS;Trusted_Connection=Yes;";
			//не пойму почему не находит строку соединения
			/*ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;*/
			List<FieldsTable> data = new List<FieldsTable>();
			using (SqlConnection connection =
				new SqlConnection(connectionString))
			{
				string queryString = "SELECT [carriage_id],  [carriage_number], [goods_weight], " +
								  "[stantion_of_destination], [code_of_receiver], [carriage_type], " +
								  "[goods_cod], [station_oper], [road_of_putting], " +
								  "[road_of_giving], [date_of_operation], [operation_number], " +
								  "[starting_station], [years_of_operation], [operation_times], " +
								  "[traveling_index], [train_index], dbo.Cargo.Name as Name_Goods, dbo.Station.Name as Name_Station, dbo.CarOperation.Name as Name_CarOper " +
								  "FROM [dbo].[carriages] " +
								  "INNER JOIN dbo.Cargo on dbo.carriages.goods_cod = dbo.Cargo.ShortCode " +
								  "INNER JOIN dbo.Station on dbo.Station.Code = dbo.carriages.stantion_of_destination " +
								  "INNER JOIN dbo.CarOperation on dbo.CarOperation.Code = dbo.carriages.operation_number";
				
				SqlCommand command = new SqlCommand(queryString, connection);
				
				try
				{
					connection.Open();
					SqlDataReader reader = command.ExecuteReader();
					
					while (reader.Read())
					{
						int index = 0;
						FieldsTable row = new FieldsTable();
 						row.carriage_id = reader.GetInt32(index++);
						row.carriage_number = reader.GetString(index++);
						row.goods_weight = reader.GetString(index++);
						row.stantion_of_destination = reader.GetString(index++); 
						row.code_of_receiver = reader.GetString(index++);
						row.carriage_type = reader.GetString(index++);
						row.goods_cod = reader.GetString(index++);  
						row.station_oper = reader.GetString(index++);  
						row.road_of_putting = reader.GetString(index++);
						row.road_of_giving = reader.GetString(index++); 
						row.date_of_operation = reader.GetString(index++);
						row.operation_number = reader.GetString(index++);
						row.starting_station = reader.GetString(index++);  
						row.years_of_operation = reader.GetString(index++); 
						row.operation_times = reader.GetString(index++);
						row.traveling_index = reader.GetString(index++);  
						row.train_index = reader.GetString(index++); 
						row.Name_Goods = reader.GetString(index++); 
						row.Name_Station = reader.GetString(index++); 
						row.Name_CarOper = reader.GetString(index++);

						data.Add(row);
					}
					reader.Close();
				}
				catch (Exception ex)
				{
					Console.WriteLine(ex.Message);
				}
				return data;
			}
        }
    }
}
